package com.example.demo.util;

import com.example.demo.entity.*;
import com.example.demo.entity.Dto.StuFourDto;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;



public class POIUtil {
    public static List<Score> excelScore(MultipartFile file) {
        Double d=new Double(0);
        List<Score> list = new ArrayList<>();
        Score score = null;
        try {
            //1. 创建一个 workbook 对象
            XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
            //2. 获取 workbook 中表单的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                //3. 获取表单
                XSSFSheet sheet = workbook.getSheetAt(i);
                //4. 获取表单中的行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    //5. 跳过标题行
                    if (j == 0) {
                        continue;//跳过标题行
                    }
                    //6. 获取行
                    XSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;//防止数据中间有空行
                    }
                    //7. 获取列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    score = new Score();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        XSSFCell cell = row.getCell(k);
                        switch (cell.getCellType()) {
                            // 类型是 String 进入此 case 块
                            case Cell.CELL_TYPE_STRING:
                                switch (k) {
                                    case 1:
                                        score.setName(cell.getStringCellValue());
                                        break;
                                    case 2:
                                        score.setMajor(cell.getStringCellValue());
                                        break;
                                    case 3:
                                        score.setClassi(cell.getStringCellValue());
                                        break;
                                    case 4:
                                        score.setCourse(cell.getStringCellValue());
                                        break;
                                    case 5:
                                        score.setTeachername(cell.getStringCellValue());
                                        break;
                                    case 10:
                                        score.setTerm(cell.getStringCellValue());
                                        break;
                                }
                                break;
                            // 类型是 Date或者数字 进入此 case 块
                            default: {
                                switch (k) {
                                    case 0:
                                        score.setId((int)(cell.getNumericCellValue()));
                                        break;
                                    case 6:
                                        d=cell.getNumericCellValue();
                                        score.setGredit(d.floatValue());
                                        break;
                                    case 7:
                                       d=cell.getNumericCellValue();
                                        score.setEscore(d.floatValue());
                                        break;
                                    case 8:
                                        d=cell.getNumericCellValue();
                                        score.setAscore(d.floatValue());
                                        break;
                                    case 9:
                                        d=cell.getNumericCellValue();
                                        score.setFscore((int)(cell.getNumericCellValue()));
                                        break;
                                }
                            }
                            break;
                        }
                    }
                    // 最后将解析后的数据添加到员工集合中
                    list.add(score);
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static List<Colleges> excelNewNeed(MultipartFile file) {
        Double d=new Double(0);
        List<Colleges> list = new ArrayList<>();
        Colleges score = null;
        try {
            //1. 创建一个 workbook 对象
            XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
            //2. 获取 workbook 中表单的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                //3. 获取表单
                XSSFSheet sheet = workbook.getSheetAt(i);
                //4. 获取表单中的行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    //5. 跳过标题行
                    if (j == 0) {
                        continue;//跳过标题行
                    }
                    //6. 获取行
                    XSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;//防止数据中间有空行
                    }
                    //7. 获取列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    score = new Colleges();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        XSSFCell cell = row.getCell(k);
                        switch (cell.getCellType()) {
                            // 类型是 String 进入此 case 块
                            case Cell.CELL_TYPE_STRING:
                                switch (k) {
                                    case 0:
                                        score.setCollege(cell.getStringCellValue());
                                        break;
                                    case 3:
                                        score.setComputerLevel(cell.getStringCellValue());
                                        break;
                                }
                                break;
                            // 类型是 Date或者数字 进入此 case 块
                            default: {
                                switch (k) {
                                    case 1:
                                        score.setGraduationGredit((float)cell.getNumericCellValue());
                                    case 2:
                                        score.setEnglishTestFour((int)(cell.getNumericCellValue()));
                                        break;
                                    case 4:
                                        score.setTotalCredit((int)(cell.getNumericCellValue()));
                                        break;
                                    case 5:
                                        score.setVersion((int) cell.getNumericCellValue()+"");
                                        break;
                                }
                            }
                            break;
                        }
                    }
                    // 最后将解析后的数据添加到员工集合中
                    list.add(score);
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        }

        return list;
    }

    /**
     * 插入每学期需要上课的课程
     * @param file
     * @return
     */
    public static List<Need> excelNewNeed1(MultipartFile file) {
        List<Need> list1 = new ArrayList<>();
        Need need=null;
        try {
            //1. 创建一个 workbook 对象
            XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
            //2. 获取 workbook 中表单的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                //3. 获取表单
                XSSFSheet sheet = workbook.getSheetAt(i);
                //4. 获取表单中的行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    //5. 跳过标题行
                    if (j == 0) {
                        continue;//跳过标题行
                    }
                    //6. 获取行
                    XSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;//防止数据中间有空行
                    }
                    //7. 获取列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    need=new Need();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        XSSFCell cell = row.getCell(k);
                        switch (cell.getCellType()) {
                            // 类型是 String 进入此 case 块
                            case Cell.CELL_TYPE_STRING:
                                switch (k) {
                                    case 0:
                                        need.setCollege(cell.getStringCellValue());
                                        break;
                                    case 6:
                                        need.setMajor(cell.getStringCellValue());
                                        break;
                                    case 7:
                                        need.setNeedCourse(cell.getStringCellValue());
                                        break;
                                    case 8:
                                        need.setGret(cell.getStringCellValue());
                                        break;
                                    case 9:
                                        need.setTerm(cell.getStringCellValue());
                                        break;
                                }
                                break;
                            // 类型是 Date或者数字 进入此 case 块
                            default: {
                                switch (k) {
                                    case 5:
                                        need.setVersion((int)cell.getNumericCellValue());
                                        break;
                                    case 10:
                                        need.setType((int)(cell.getNumericCellValue()));
                                        break;
                                }
                            }
                            break;
                        }
                    }
                    // 最后将解析后的数据添加到员工集合中
                    list1.add(need);
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        }

        return list1;
    }

    public static List<Grade> excelGrade(MultipartFile file) {
        List<Grade> list1 = new ArrayList<>();
        Grade need=null;
        try {
            //1. 创建一个 workbook 对象
            XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
            //2. 获取 workbook 中表单的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                //3. 获取表单
                XSSFSheet sheet = workbook.getSheetAt(i);
                //4. 获取表单中的行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    //5. 跳过标题行
                    if (j == 0) {
                        continue;//跳过标题行
                    }
                    //6. 获取行
                    XSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;//防止数据中间有空行
                    }
                    //7. 获取列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    need=new Grade();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        XSSFCell cell = row.getCell(k);
                        switch (cell.getCellType()) {
                            // 类型是 String 进入此 case 块
                            case Cell.CELL_TYPE_STRING:
                                switch (k) {
                                    case 2:
                                        need.setGrade(cell.getStringCellValue());
                                        break;
                                    case 3:
                                        need.setCollege(cell.getStringCellValue());
                                        break;
                                }
                                break;
                            // 类型是 Date或者数字 进入此 case 块
                            default: {
                                switch (k) {
                                    case 5:
                                        need.setGrade(cell.getNumericCellValue()+ "级");
                                        break;
                                }
                            }
                            break;
                        }
                    }
                    // 最后将解析后的数据添加到员工集合中
                    list1.add(need);
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        }

        return list1;
    }

    public static List<StuExternal> excelFourScore(MultipartFile file) {
        List<StuExternal> list1 = new ArrayList<>();
        StuExternal need=null;
        try {
            //1. 创建一个 workbook 对象
            Workbook workbook = WorkbookFactory.create(file.getInputStream());
           // XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
            //2. 获取 workbook 中表单的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                //3. 获取表单
                Sheet sheet = workbook.getSheetAt(i);
                //4. 获取表单中的行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    //5. 跳过标题行
                    if (j == 0) {
                        continue;//跳过标题行
                    }
                    //6. 获取行
                    Row row = sheet.getRow(j);
                    if (row == null) {
                        continue;//防止数据中间有空行
                    }
                    //7. 获取列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    need=new StuExternal();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        Cell cell = row.getCell(k);
                        switch (cell.getCellType()) {
                            // 类型是 String 进入此 case 块
                            case Cell.CELL_TYPE_STRING:
                                switch (k) {
                                    case 1:
                                        need.setTerm(cell.getStringCellValue());
                                        break;
                                    case 3:
                                        need.setComputerLevel(cell.getStringCellValue());
                                        break;
                                }
                                break;
                            // 类型是 Date或者数字 进入此 case 块
                            default: {
                                switch (k) {
                                    case 0:
                                        need.setId((int)(cell.getNumericCellValue()));
                                        break;
                                    case 2:
                                        need.setEnglishTestFour((int)(cell.getNumericCellValue()));
                                        break;
                                }
                            }
                            break;
                        }
                    }
                    // 最后将解析后的数据添加到员工集合中
                    list1.add(need);
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }

        return list1;
    }

}
